package org.ekingstar.component.excel;

//需要poi jar包
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections.map.ListOrderedMap;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;


public class ExportExcel {
	/**
	 * 
	 * @param response		HttpServletResponse
	 * @param columnName	要显示的字段名称
	 * @param columnNickName	要显示列的封在hsql List里的Map里的字段别名
	 * @param objectList		扔到页面List集合 
	 */
	public void exportExcel( HttpServletResponse response,List columnNames,List columnNickName,List objectList){
		
		// Create the Excel File
		String fileName="excel.xls";
		
		// Create the Sheet with the name of the Table
		HSSFWorkbook wb = new HSSFWorkbook();
		// 设置显示的字体以及相关的样式
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 8);
		font.setItalic(false);
		font.setStrikeout(false);

		// 设置标题栏单元格的样式
		HSSFCellStyle cellHeadStyle = wb.createCellStyle();
		cellHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
		cellHeadStyle.setBorderLeft((short) 1);
		cellHeadStyle.setBorderRight((short) 1);
		cellHeadStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
		cellHeadStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		cellHeadStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		// 设置数据行的字体以及以及相关的对齐方式
		HSSFCellStyle cellDataStyle = wb.createCellStyle();
		cellDataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellDataStyle.setFont(font);

		HSSFSheet sheet1 = wb.createSheet("Sheet1");

		// Grab & populate the Column Names
		HSSFRow row = sheet1.createRow((short) 0);
		// Populate the first row of the spreadsheet with the column names

		for (int i = 0; i < columnNames.size(); i++) {
			HSSFCell cname = row.createCell((short) i);
			cname.setCellStyle(cellHeadStyle);
			cname.setEncoding(HSSFCell.ENCODING_UTF_16);
			cname.setCellValue((String) columnNames.get(i));
		}

		// Populate the Row Data to the SpreadSheet
		int rowCount = objectList.size();
		// goal houses the number of cells in the target spreadsheet
		int goal = rowCount * columnNames.size();

		// actual population of target spreadsheet
		int currentCell = 0;

		for (int i = 0; i < rowCount; i++) {
			Map tableData = (Map) objectList.get(i);		
			row = sheet1.createRow((short) (i + 1));
			for(int j=0;j<columnNames.size();j++){		
				HSSFCell ctitle = row.createCell((short) j);
				ctitle.setCellStyle(cellDataStyle);
				ctitle.setEncoding(HSSFCell.ENCODING_UTF_16);
				Object v = tableData.get(columnNickName.get(j));
				if(v!=null){
					ctitle.setCellValue(v.toString());
				}else{
					ctitle.setCellValue("");
				}
				
			}
			currentCell++;

		}
		OutputStream os;
		try {
			response.setContentType("application/x-msdownload");
			response.setHeader("Content-Disposition", "attachment;"
					+ " filename="
					+ new String(fileName.getBytes(), "ISO-8859-1"));
			os = response.getOutputStream();
			wb.write(os);
			
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
			
		} catch (IOException e) {
			e.printStackTrace();
																			
		}	
		
	}
	
	/**
	 * 
	 * @param response		HttpServletResponse
	 * @param columnName	要显示的字段名称
	 * @param columnNickName	要显示列的封在hsql List里的Map里的字段别名
	 * @param objectList		扔到页面List集合 
	 * @prram fileName          文件名称
	 */
	public void exportExcel( HttpServletResponse response,List columnNames,List columnNickName,List objectList,String fileName){
		
		// Create the Excel File
		//String fileName="excel.xls";
		
		// Create the Sheet with the name of the Table
		HSSFWorkbook wb = new HSSFWorkbook();
		// 设置显示的字体以及相关的样式
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 8);
		font.setItalic(false);
		font.setStrikeout(false);

		// 设置标题栏单元格的样式
		HSSFCellStyle cellHeadStyle = wb.createCellStyle();
		cellHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
		cellHeadStyle.setBorderLeft((short) 1);
		cellHeadStyle.setBorderRight((short) 1);
		cellHeadStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
		cellHeadStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		cellHeadStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		// 设置数据行的字体以及以及相关的对齐方式
		HSSFCellStyle cellDataStyle = wb.createCellStyle();
		cellDataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellDataStyle.setFont(font);

		HSSFSheet sheet1 = wb.createSheet("Sheet1");

		// Grab & populate the Column Names
		HSSFRow row = sheet1.createRow((short) 0);
		// Populate the first row of the spreadsheet with the column names

		for (int i = 0; i < columnNames.size(); i++) {
			HSSFCell cname = row.createCell((short) i);
			cname.setCellStyle(cellHeadStyle);
			cname.setEncoding(HSSFCell.ENCODING_UTF_16);
			cname.setCellValue((String) columnNames.get(i));
		}

		// Populate the Row Data to the SpreadSheet
		int rowCount = objectList.size();
		// goal houses the number of cells in the target spreadsheet
		int goal = rowCount * columnNames.size();

		// actual population of target spreadsheet
		int currentCell = 0;

		for (int i = 0; i < rowCount; i++) {
			Map tableData = (Map) objectList.get(i);		
			row = sheet1.createRow((short) (i + 1));
			for(int j=0;j<columnNames.size();j++){		
				HSSFCell ctitle = row.createCell((short) j);
				ctitle.setCellStyle(cellDataStyle);
				ctitle.setEncoding(HSSFCell.ENCODING_UTF_16);
				Object v = tableData.get(columnNickName.get(j));
				if(v!=null){
					ctitle.setCellValue(v.toString());
				}else{
					ctitle.setCellValue("");
				}
				
			}
			currentCell++;

		}
		OutputStream os;
		try {
			response.setContentType("application/x-msdownload");
			response.setHeader("Content-Disposition", "attachment;"
					+ " filename="
					+ new String(fileName.getBytes(), "ISO-8859-1"));
			os = response.getOutputStream();
			wb.write(os);
			
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
			
		} catch (IOException e) {
			e.printStackTrace();
																			
		}	
		
	}
	/**
	 * 
	 * @param response		HttpServletResponse
	 * @param columnName	要显示的字段名称
	 * @param columnNickName	要显示列的封在hsql List里的Map里的字段别名
	 * @param objectList		扔到页面List集合 
	 */
	public void exportExcel( HttpServletResponse response,String[] columnName,String[] columnNickName,List objectList){
		
		// Create the Excel File
		String fileName="excel.xls";
		
		// Create the Sheet with the name of the Table
		HSSFWorkbook wb = new HSSFWorkbook();
		// 设置显示的字体以及相关的样式
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 8);
		font.setItalic(false);
		font.setStrikeout(false);
	
		// 设置标题栏单元格的样式
		HSSFCellStyle cellHeadStyle = wb.createCellStyle();
		cellHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
		cellHeadStyle.setBorderLeft((short) 1);
		cellHeadStyle.setBorderRight((short) 1);
		cellHeadStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
		cellHeadStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		cellHeadStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	
		// 设置数据行的字体以及以及相关的对齐方式
		HSSFCellStyle cellDataStyle = wb.createCellStyle();
		cellDataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellDataStyle.setFont(font);
	
		HSSFSheet sheet1 = wb.createSheet("Sheet1");
	
		// Grab & populate the Column Names
		HSSFRow row = sheet1.createRow((short) 0);
		// Populate the first row of the spreadsheet with the column names
		List columnNames = new ArrayList();
		columnNames=Arrays.asList(columnName);
	
		for (int i = 0; i < columnNames.size(); i++) {
			HSSFCell cname = row.createCell((short) i);
			cname.setCellStyle(cellHeadStyle);
			cname.setEncoding(HSSFCell.ENCODING_UTF_16);
			cname.setCellValue((String) columnNames.get(i));
		}
	
		// Populate the Row Data to the SpreadSheet
		int rowCount = objectList.size();
		// goal houses the number of cells in the target spreadsheet
		int goal = rowCount * columnNames.size();
	
		// actual population of target spreadsheet
		int currentCell = 0;
	
		for (int i = 0; i < rowCount; i++) {
			Map tableData = (Map) objectList.get(i);		
			row = sheet1.createRow((short) (i + 1));
			for(int j=0;j<columnNames.size();j++){		
				HSSFCell ctitle = row.createCell((short) j);
				ctitle.setCellStyle(cellDataStyle);
				ctitle.setEncoding(HSSFCell.ENCODING_UTF_16);
				ctitle.setCellValue((String)tableData.get(columnNickName[j]));
			}
			currentCell++;
	
		}
		OutputStream os;
		try {
			response.setContentType("application/x-msdownload");
			response.setHeader("Content-Disposition", "attachment;"
					+ " filename="
					+ new String(fileName.getBytes(), "ISO-8859-1"));
			os = response.getOutputStream();
			wb.write(os);
			
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
			
		} catch (IOException e) {
			e.printStackTrace();
																			
		}	
	}
	
	
	/**
	 * 
	 * @param response		HttpServletResponse
	 * @param columnName	要显示的字段名称
	 * @param objectList		扔到页面List集合 
	 */
	public void exportExcel( HttpServletResponse response,String[] columnName,List objectList){
		
		// Create the Excel File
		String fileName="excel.xls";
		
		// Create the Sheet with the name of the Table
		HSSFWorkbook wb = new HSSFWorkbook();
		// 设置显示的字体以及相关的样式
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 8);
		font.setItalic(false);
		font.setStrikeout(false);
	
		// 设置标题栏单元格的样式
		HSSFCellStyle cellHeadStyle = wb.createCellStyle();
		cellHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
		cellHeadStyle.setBorderLeft((short) 1);
		cellHeadStyle.setBorderRight((short) 1);
		cellHeadStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
		cellHeadStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		cellHeadStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	
		// 设置数据行的字体以及以及相关的对齐方式
		HSSFCellStyle cellDataStyle = wb.createCellStyle();
		cellDataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellDataStyle.setFont(font);
	
		HSSFSheet sheet1 = wb.createSheet("Sheet1");
	
		// Grab & populate the Column Names
		HSSFRow row = sheet1.createRow((short) 0);
		// Populate the first row of the spreadsheet with the column names
		List columnNames = new ArrayList();
		columnNames=Arrays.asList(columnName);
	
		for (int i = 0; i < columnNames.size(); i++) {
			HSSFCell cname = row.createCell((short) i);
			cname.setCellStyle(cellHeadStyle);
			cname.setEncoding(HSSFCell.ENCODING_UTF_16);
			cname.setCellValue((String) columnNames.get(i));
		}
	
		// Populate the Row Data to the SpreadSheet
		int rowCount = objectList.size();
		// goal houses the number of cells in the target spreadsheet
		int goal = rowCount * columnNames.size();
	
		// actual population of target spreadsheet
		int currentCell = 0;
	
		for (int i = 0; i < rowCount; i++) {
			ListOrderedMap lom = (ListOrderedMap)objectList.get(i);	
			row = sheet1.createRow((short) (i + 1));
			for(int j=0;j<columnNames.size();j++){	
				HSSFCell ctitle = row.createCell((short) j);
				ctitle.setCellStyle(cellDataStyle);
				ctitle.setEncoding(HSSFCell.ENCODING_UTF_16);
				ctitle.setCellValue((String)lom.get(lom.get(j)));
			}
			currentCell++;	
		}
		
		OutputStream os;
		try {
			response.setContentType("application/x-msdownload");
			response.setHeader("Content-Disposition", "attachment;"
					+ " filename="
					+ new String(fileName.getBytes(), "ISO-8859-1"));
			os = response.getOutputStream();
			wb.write(os);
			
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
			
		} catch (IOException e) {
			e.printStackTrace();
																			
		}	
	}

}
